USE [BS2]
GO
/****** Object:  View [dbo].[vw_VouchersDetail]    Script Date: 08/09/2015 08:04:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_VouchersDetail]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, 
                      dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, 
                      dbo.VoucherDetails.Remarks, dbo.VoucherDetails.Amount AS Debit, 0 AS Credit, dbo.VoucherDetails.DrOrCr, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SessionID, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.VoucherDetails.ItemID, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.InUnit, dbo.VoucherDetails.BatchNo, dbo.VoucherDetails.Qty + dbo.VoucherDetails.Bonus AS Qty, 
					  dbo.VoucherDetails.Qty AS SQTY, dbo.VoucherDetails.Bonus, dbo.VoucherDetails.Qty + dbo.VoucherDetails.Bonus AS InQty, 0 AS OutQty, 
					  dbo.VoucherDetails.Rate, dbo.VoucherDetails.Amount
FROM         dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Dr')
UNION ALL
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherNo, dbo.Voucher.VoucherDate, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, 
                      dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, 
                      dbo.VoucherDetails.Remarks, 0 AS Debit, dbo.VoucherDetails.Amount AS Credit, dbo.VoucherDetails.DrOrCr, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.SessionID, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      ISNULL(dbo.Voucher.CustomerName, '') AS CustomerName, dbo.Voucher.Expense, dbo.Voucher.Discount, dbo.VoucherDetails.ItemID, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.InUnit, dbo.VoucherDetails.BatchNo, dbo.VoucherDetails.Qty + dbo.VoucherDetails.Bonus AS Qty, 
					  dbo.VoucherDetails.Qty AS SQTY, dbo.VoucherDetails.Bonus,0 AS InQty, dbo.VoucherDetails.Qty + dbo.VoucherDetails.Bonus AS OutQty, 
					  dbo.VoucherDetails.Rate, dbo.VoucherDetails.Amount
FROM         dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails.VoucherID = dbo.Voucher.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
WHERE     (dbo.VoucherDetails.DrOrCr = N'Cr')